
CREATE TABLE Author 
(
   Id  int  NOT NULL PRIMARY KEY identity,
   FirstName  nvarchar(45) NOT NULL,
   LastName  nvarchar(45) NOT NULL,
   UpdatedTime  datetime NOT NULL
 )



 --2

 
-- ְԱ��
CREATE TABLE Employees
(
   Id INT PRIMARY KEY NOT NULL identity,
   Name nvarchar(80) NOT NULL,
   Age INT NOT NULL,
   Address NVARCHAR(50),
   SALARY decimal(18,2)
);
-- �����־��
CREATE TABLE AuditLog
(
   Id int primary key not null identity,
   NAME TEXT NOT NULL,
   Salary decimal(18,2)
);




go
create trigger trg_AuditLog         -----������
on Employees
instead of insert 
as
begin
	select * from deleted
	select * from inserted
end
go


 INSERT INTO Employees (NAME,AGE,ADDRESS,SALARY)VALUES ('Paul', 32, 'California', 20000.00 );

select * from AuditLog;





create database OADatabase

go

if exists (select 1
            from  sysobjects
           where  id = object_id('Department')
            and   type = 'U')
   drop table Department
go

/*==============================================================*/
/* Table: Department                                            */
/*==============================================================*/
create table Department (
   BusinessMent         nvarchar(90)         null,
   PlanningMent         nvarchar((90)        null,
   OtherMent            nvarchar(90)         null,
   DepartmentId         int                  identity,
   constraint PK_DEPARTMENT primary key (DepartmentId)
)
go



if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('departmentInfo') and o.name = 'FK_DEPARTME_REFERENCE_DEPARTME')
alter table departmentInfo
   drop constraint FK_DEPARTME_REFERENCE_DEPARTME
go

if exists (select 1
            from  sysobjects
           where  id = object_id('departmentInfo')
            and   type = 'U')
   drop table departmentInfo
go

/*==============================================================*/
/* Table: departmentInfo                                        */
/*==============================================================*/
create table departmentInfo (
   MengId               int                  not null,
   MendName             nvnvarchar(90)       null,
   Charge               nvarchar(90)         null,
   TelePhone            int                  null,
   MailBox              int                  null,
   constraint PK_DEPARTMENTINFO primary key (MengId),
   constraint AK_KEY_1_DEPARTME unique ()
)
go

alter table departmentInfo
   add constraint FK_DEPARTME_REFERENCE_DEPARTME foreign key (MengId)
      references Department (DepartmentId)
go




if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('StaffInfo') and o.name = 'FK_STAFFINF_REFERENCE_DEPARTME')
alter table StaffInfo
   drop constraint FK_STAFFINF_REFERENCE_DEPARTME
go

if exists (select 1
            from  sysobjects
           where  id = object_id('StaffInfo')
            and   type = 'U')
   drop table StaffInfo
go

/*==============================================================*/
/* Table: StaffInfo                                             */
/*==============================================================*/
create table StaffInfo (
   Staffid              int                  identity,
   StaffName            nvarchar(90)         null,
   StaffPosition        nvarchar(90)         null,
   StaffRealtion        int                  null,
   StaffPay             int                  null,
   constraint PK_STAFFINFO primary key (Staffid)
)
go

alter table StaffInfo
   add constraint FK_STAFFINF_REFERENCE_DEPARTME foreign key (Staffid)
      references departmentInfo (MengId)
go





if exists (select 1
            from  sysobjects
           where  id = object_id('UserInfo')
            and   type = 'U')
   drop table UserInfo
go

/*==============================================================*/
/* Table: UserInfo                                              */
/*==============================================================*/
create table UserInfo (
   UserId               int                  identity,
   UserName             nvarchar(90)         null,
   BankId               int                  null,
   TelePhone            int                  null,
   Address              nvarchar(90)         null,
   constraint PK_USERINFO primary key (UserId)
)
go


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ApplyInfo') and o.name = 'FK_APPLYINF_REFERENCE_USERINFO')
alter table ApplyInfo
   drop constraint FK_APPLYINF_REFERENCE_USERINFO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ApplyInfo')
            and   type = 'U')
   drop table ApplyInfo
go

/*==============================================================*/
/* Table: ApplyInfo                                             */
/*==============================================================*/
create table ApplyInfo (
   ApplyId              int                  identity,
   UserId               int                  null,
   ConfenceDate         char(90)             null,
   Day                  char(90)             null,
   ConfenceNumber       int                  null,
   Site                 nvarchar(90)         null,
   Budget               char(90)             null,
   AcceptLoge           nvarchar(90)          null,
   Column_9             char(10)             null,
   constraint PK_APPLYINFO primary key (ApplyId)
)
go

alter table ApplyInfo
   add constraint FK_APPLYINF_REFERENCE_USERINFO foreign key (UserId)
      references UserInfo (UserId)
go



if exists (select 1
            from  sysobjects
           where  id = object_id('PlanTask')
            and   type = 'U')
   drop table PlanTask
go

/*==============================================================*/
/* Table: PlanTask                                              */
/*==============================================================*/
create table PlanTask (
   ApplyId              int                  null,
   TaskDetall           nvarchar(90)         null,
   RequireTime          char(90)             null
)
go




create trigger tr_ApplyInfo
on ApplyInfo
instead of insert 
as
begin
	set nocount on
	declare @state nvarchar(90),@count int
	select @count=COUNT(*) where inserted

	if(@count>0)
		begin
			print'������'
		end
	else 
		begin
			select @state
		end
	set nocount off
end
go
		



go

create proc proc_ApplyInfo
@state nvarchar(90),
@date date
as
begin
	select * from ApplyInfo
	where @date=ConfenceDate or @state=AcceptLoge
end
go
